﻿Imports System.Collections.Generic
Imports System.Data
Imports System.Data.SqlClient


''' <summary>
''' Clase de la capa de acceso a datos DAL que implementa el patrón Table Data Gateway para el manejo de la entidad Usuario
''' </summary>
''' <remarks></remarks>
Public Class TDGUsuario

    Private Shared m_Instance As TDGUsuario = Nothing


    Public Function Insert(conn As SqlConnection, _
                           Usu_Legajo As Int16, _
                           Usu_Nombre As String, _
                           Usu_Apellido As String, _
                           Idi_Codigo As Int16, _
                           DVH As Int32) As Int16
        Dim command As New SqlCommand
        Dim iRowsAffected As Int16


        command.Connection = conn
        command.CommandType = CommandType.StoredProcedure

        command.CommandText = "dbo.addUsuario"
        command.Parameters.AddWithValue("@Usu_Legajo", Usu_Legajo)
        command.Parameters.AddWithValue("@Usu_Nombre", Usu_Nombre)
        command.Parameters.AddWithValue("@Usu_Apellido", Usu_Apellido)
        command.Parameters.AddWithValue("@Idi_Codigo", Idi_Codigo)
        command.Parameters.AddWithValue("@DVH", DVH)

        iRowsAffected = command.ExecuteNonQuery()

        Return iRowsAffected

    End Function

    Public Function Update(conn As SqlConnection, _
                           Usu_Legajo As Int16, _
                           Usu_Nombre As String, _
                           Usu_Apellido As String, _
                           Idi_Codigo As Int16, _
                           EstU_Codigo As String) As Int16
        Dim command As New SqlCommand
        Dim iRowsAffected As Int16


        command.Connection = conn
        command.CommandType = CommandType.StoredProcedure

        command.CommandText = "dbo.updUsuario"
        command.Parameters.AddWithValue("@Usu_Legajo", Usu_Legajo)
        command.Parameters.AddWithValue("@Usu_Nombre", Usu_Nombre)
        command.Parameters.AddWithValue("@Usu_Apellido", Usu_Apellido)
        command.Parameters.AddWithValue("@Idi_Codigo", Idi_Codigo)
        command.Parameters.AddWithValue("@EstU_Codigo", EstU_Codigo)

        iRowsAffected = command.ExecuteNonQuery()

        Return iRowsAffected

    End Function

    Public Function Update_Idioma(conn As SqlConnection, _
                                  Usu_Legajo As Int16, _
                                  Idi_Codigo As Int16) As Int16
        Dim command As New SqlCommand
        Dim iRowsAffected As Int16


        command.Connection = conn
        command.CommandType = CommandType.StoredProcedure

        command.CommandText = "dbo.updUsuario_CambiarIdioma"
        command.Parameters.AddWithValue("@Usu_Legajo", Usu_Legajo)
        command.Parameters.AddWithValue("@Idi_Codigo", Idi_Codigo)

        iRowsAffected = command.ExecuteNonQuery()

        Return iRowsAffected

    End Function

    Public Function Update_Baja(conn As SqlConnection, _
                                Usu_Legajo As Int16) As Int16
        Dim command As New SqlCommand
        Dim iRowsAffected As Int16

        command.Connection = conn
        command.CommandType = CommandType.StoredProcedure

        command.CommandText = "dbo.updUsuario_Baja"
        command.Parameters.AddWithValue("@Usu_Legajo", Usu_Legajo)

        iRowsAffected = command.ExecuteNonQuery()

        Return iRowsAffected

    End Function

    Public Function Update_EstadoBloquear(conn As SqlConnection, _
                            Usu_Legajo As Int16) As Int16
        Dim command As New SqlCommand
        Dim iRowsAffected As Int16

        command.Connection = conn
        command.CommandType = CommandType.StoredProcedure

        command.CommandText = "dbo.updUsuario_Bloquear"
        command.Parameters.AddWithValue("@Usu_Legajo", Usu_Legajo)

        iRowsAffected = command.ExecuteNonQuery()

        Return iRowsAffected

    End Function

    Public Function Update_EstadoActivar(conn As SqlConnection, _
                        Usu_Legajo As Int16) As Int16
        Dim command As New SqlCommand
        Dim iRowsAffected As Int16

        command.Connection = conn
        command.CommandType = CommandType.StoredProcedure

        command.CommandText = "dbo.updUsuario_Activar"
        command.Parameters.AddWithValue("@Usu_Legajo", Usu_Legajo)

        iRowsAffected = command.ExecuteNonQuery()

        Return iRowsAffected

    End Function

    Public Function Find(conn As SqlConnection, Usu_Legajo As Int16) As SqlDataReader
        Dim command As New SqlCommand
        Dim dr As SqlDataReader


        command.Connection = conn
        command.CommandType = CommandType.StoredProcedure

        command.CommandText = "dbo.getUsuario"
        command.Parameters.AddWithValue("@Usu_Legajo", Usu_Legajo)

        dr = command.ExecuteReader()

        Return dr

    End Function

    Public Function FindAll(conn As SqlConnection) As SqlDataReader
        Dim command As New SqlCommand
        Dim dr As SqlDataReader


        command.Connection = conn
        command.CommandType = CommandType.StoredProcedure

        command.CommandText = "dbo.getUsuario_All"

        dr = command.ExecuteReader()

        Return dr

    End Function

    Public Function Find_Patentes(conn As SqlConnection, Usu_Legajo As Int16) As SqlDataReader
        Dim command As New SqlCommand
        Dim dr As SqlDataReader


        command.Connection = conn
        command.CommandType = CommandType.StoredProcedure

        command.CommandText = "dbo.getUsuario_Patentes"
        command.Parameters.AddWithValue("@Usu_Legajo", Usu_Legajo)

        dr = command.ExecuteReader()

        Return dr

    End Function

    Public Function Find_Familias(conn As SqlConnection, Usu_Legajo As Int16) As SqlDataReader
        Dim command As New SqlCommand
        Dim dr As SqlDataReader


        command.Connection = conn
        command.CommandType = CommandType.StoredProcedure

        command.CommandText = "dbo.getUsuario_Familias"
        command.Parameters.AddWithValue("@Usu_Legajo", Usu_Legajo)

        dr = command.ExecuteReader()

        Return dr

    End Function

    Public Function Insert_Patente(conn As SqlConnection, _
                                   Usu_Legajo As Int16, _
                                   Pat_Codigo As Int16) As Int16
        Dim command As New SqlCommand
        Dim iRowsAffected As Int16

        command.Connection = conn
        command.CommandType = CommandType.StoredProcedure

        command.CommandText = "dbo.addPatente_Usuario"
        command.Parameters.AddWithValue("@Usu_Legajo", Usu_Legajo)
        command.Parameters.AddWithValue("@Pat_Codigo", Pat_Codigo)

        iRowsAffected = command.ExecuteNonQuery()

        Return iRowsAffected

    End Function

    Public Function Insert_Familia(conn As SqlConnection, _
                                   Usu_Legajo As Int16, _
                                   Fam_Codigo As Int16) As Int16
        Dim command As New SqlCommand
        Dim iRowsAffected As Int16


        command.Connection = conn
        command.CommandType = CommandType.StoredProcedure

        command.CommandText = "dbo.addFam_Usuario"
        command.Parameters.AddWithValue("@Usu_Legajo", Usu_Legajo)
        command.Parameters.AddWithValue("@Fam_Codigo", Fam_Codigo)

        iRowsAffected = command.ExecuteNonQuery()

        Return iRowsAffected

    End Function

    Public Function Delete_Patente(conn As SqlConnection, _
                                   Usu_Legajo As Int16, _
                                   Pat_Codigo As Int16) As Int16
        Dim command As New SqlCommand
        Dim iRowsAffected As Int16


        command.Connection = conn
        command.CommandType = CommandType.StoredProcedure
        command.CommandText = "dbo.delPatente_Usuario"
        command.Parameters.AddWithValue("@Usu_Legajo", Usu_Legajo)
        command.Parameters.AddWithValue("@Pat_Codigo", Pat_Codigo)

        iRowsAffected = command.ExecuteNonQuery()

        Return iRowsAffected

    End Function

    Public Function Delete_Familia(conn As SqlConnection, _
                                   Usu_Legajo As Int16, _
                                   Fam_Codigo As Int16) As Int16
        Dim command As New SqlCommand
        Dim iRowsAffected As Int16


        command.Connection = conn
        command.CommandType = CommandType.StoredProcedure
        command.CommandText = "dbo.delFam_Usuario"
        command.Parameters.AddWithValue("@Usu_Legajo", Usu_Legajo)
        command.Parameters.AddWithValue("@Fam_Codigo", Fam_Codigo)

        iRowsAffected = command.ExecuteNonQuery()

        Return iRowsAffected

    End Function

    Public Function TienePermiso(conn As SqlConnection, Usu_Legajo As Int16, Pat_Codigo As Int16) As Boolean
        Dim command As New SqlCommand
        Dim outParam_iTienePermiso As SqlParameter


        command.Connection = conn
        command.CommandType = CommandType.StoredProcedure

        command.CommandText = "dbo.getTienePerm"
        command.Parameters.AddWithValue("@Usu_Legajo", Usu_Legajo)
        command.Parameters.AddWithValue("@Pat_Codigo", Pat_Codigo)

        outParam_iTienePermiso = command.Parameters.Add("@iTienePermiso", SqlDbType.SmallInt)
        outParam_iTienePermiso.Direction = ParameterDirection.Output

        command.ExecuteNonQuery()

        ' Retorno valores
        TienePermiso = (outParam_iTienePermiso.Value = 1)

    End Function

    Public Shared ReadOnly Property Instance()
        Get
            If (m_Instance Is Nothing) Then
                m_Instance = New TDGUsuario()
            End If

            Return m_Instance
        End Get
    End Property

    Private Sub New()

    End Sub

End Class
